using System;
using System.Collections.Generic;
using System.Text;

using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;


namespace modifyexpression
{
    /// <summary>
    /// This demo illustrate how to modify expression in sql statement.
    /// A typical scenario is modify condition in where clause
    /// 
    /// In this demo,
    /// 1. parse sql statement
    /// 2. get where condition
    /// 3. traverse expression in pre/in/post order to find expression need to be modified
    /// 4. modify expression
    /// 5. get modified sql
    /// 
    /// </summary>
  
    class modifyexpression
    {
        static void Main(string[] args)
        {
            addCondtion();
           // removeCondition();
           // replaceCondition();
           // customizedModifyDemo();

        }

        static void replaceCondition()
        {
            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);//DbVOracle

            sqlparser.SqlText.Text = @"SELECT ClientID, FirstName + ' ' + LastName AS FullName, Phone
FROM Client
WHERE ClientID IN ( 1, 2, 3, 4 ) and ClientID > 2";

            int i = sqlparser.Parse();
            if (i == 0)
            {
                TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.SqlStatements[0];
                whereCondition w = new whereCondition(select.WhereClause);
                w.replace("ClientID > 2", "ClientID > 4");
                Console.WriteLine(pp(sqlparser.SqlStatements[0].AsText, TDbVendor.DbVMssql));
            }
            else
                Console.WriteLine(sqlparser.ErrorMessages);

        }

        /// <summary>
        /// remove a specified condition in where clause
        /// 
        /// </summary>
        static void removeCondition()
        {
            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);//DbVOracle

            sqlparser.SqlText.Text = @"SELECT ClientID, FirstName + ' ' + LastName AS FullName, Phone
FROM Client
WHERE ClientID IN ( 1, 2, 3, 4 ) and ClientID > 2";

            int i = sqlparser.Parse();
            if (i == 0)
            {
                TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.SqlStatements[0];
                whereCondition w = new whereCondition(select.WhereClause);
                w.remove("ClientID > 2");
                Console.WriteLine(pp(sqlparser.SqlStatements[0].AsText, TDbVendor.DbVMssql));
            }
            else
                Console.WriteLine(sqlparser.ErrorMessages);
        }

        /// <summary>
        /// add new condition in where clause if this condition not exists already
        /// </summary>
        static void addCondtion()
        {
            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);//DbVOracle

            sqlparser.SqlText.Text = @"SELECT ClientID, FirstName + ' ' + LastName AS FullName, Phone
FROM Client
WHERE ClientID IN ( 1, 2, 3, 4 )";

            int i = sqlparser.Parse();
            if (i == 0)
            {
                TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.SqlStatements[0];
                whereCondition w = new whereCondition(select.WhereClause);
                w.add(conditionType.and, "ClientID > 2");
                Console.WriteLine(pp(sqlparser.SqlStatements[0].AsText, TDbVendor.DbVMssql));
                w.add(conditionType.and, "ClientID > 2"); //already exists, will not add it again
                Console.WriteLine(pp(sqlparser.SqlStatements[0].AsText, TDbVendor.DbVMssql));
                w.add(conditionType.or, "ClientID > 3"); // add it 
                Console.WriteLine(pp(sqlparser.SqlStatements[0].AsText, TDbVendor.DbVMssql));
            }
            else
                Console.WriteLine(sqlparser.ErrorMessages);

        }

        /// <summary>
        /// This demo illustrate how to modify existing condition in where clause.
        /// 1. transform IN condition from
        /// tblPoints.Zone In ('mid 1','mid 2','mid 3','mid 4','mid 5','mid 6','mid 7')
        /// to syntax like this:
        /// tblPoints.Zone.In ["mid 1","mid 2","mid 3","mid 4","mid 5","mid 6","mid 7"]
        /// 
        /// 2. remove tblPipelines.ID = vwFlowDataBest.PipelineID in AND condition
        /// </summary>
        static void customizedModifyDemo()
        {

            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);//DbVOracle

            sqlparser.SqlText.Text = @"SELECT vwFlowDataBest.MeasurementDate As 'ActualDate', vwFlowDataBest.LocationType, vwFlowDataBest.ScheduledVolume, tblPoints.Zone, tblPoints.Name As 'PointName', vwFlowDataBest.PointID, tblCustomerType.Name, vwFlowDataBest.OperationallyAvailable, tblPoints.County, tblPoints.State, tblConnectingParty.Name
FROM Pipe2Pipe.dbo.tblConnectingParty tblConnectingParty, Pipe2Pipe.dbo.tblCustomerType tblCustomerType, Pipe2Pipe.dbo.tblPipelines tblPipelines, Pipe2Pipe.dbo.tblPoints tblPoints, Pipe2Pipe.dbo.vwFlowDataBest vwFlowDataBest
WHERE tblCustomerType.ID = tblPoints.BentekCustomerTypeID 
AND tblPipelines.ID = vwFlowDataBest.PipelineID 
AND tblPoints.ID = vwFlowDataBest.PointID 
AND tblPoints.BentekConnectingPartyID = tblConnectingParty.ID 
AND ((tblPipelines.ID=16) AND (vwFlowDataBest.ScheduledVolume<>0) 
AND (tblPoints.Zone In ('mid 1','mid 2','mid 3','mid 4','mid 5','mid 6','mid 7')) 
AND (tblCustomerType.ID=16) 
--AND (vwFlowDataBest.MeasurementDate>={ts '2010-05-15 00:00:00'}) 
AND (tblPipelines.ID<155))
";

            Console.WriteLine("Processing..");

            int i = sqlparser.Parse();
            if (i == 0)
            {
                whereCondition w = new whereCondition(sqlparser.SqlStatements[0].WhereClause);
                w.modify1();
                // output modified sql text
                Console.WriteLine(sqlparser.SqlStatements[0].AsText);
            }
            else
                Console.WriteLine(sqlparser.ErrorMessages);

        }

        static string pp(string sql, TDbVendor vendor)
        {
            TGSqlParser parser = new TGSqlParser(vendor);
            parser.SqlText.Text = sql;
            if (parser.PrettyPrint() == 0)
            {
                return parser.FormattedSqlText.Text;
            }
            else
            {
                return sql;
            }

        }


    }

    enum conditionType { and,or };

    class whereCondition
    {

        private TLzCustomExpression condition;
        public whereCondition(TLzCustomExpression expr)
        {
            this.condition = expr;
        }

        public void replace(string oldExpr, string newExpr){
            this.searchText = oldExpr;
            this.replaceText = newExpr;
            this.condition.InOrderTraverse(searchThenReplaceVisitor);
        }

        public void remove(string exprStr)
        {
            this.searchText = exprStr;
            this.condition.InOrderTraverse(searchThenRemoveVisitor);
        }

        public Boolean searchThenReplaceVisitor(TLz_Node pnode, Boolean pIsLeafNode)
        {
            TLzCustomExpression lcexpr = (TLzCustomExpression)pnode;
            if (lcexpr.AsText.Replace(" ", "").CompareTo(searchText.Replace(" ", "")) == 0)
            {
                lcexpr.AsText = replaceText; //
                return false; // stop searching
            }
            else
            {
                return true;
            }
        }

        public Boolean searchThenRemoveVisitor(TLz_Node pnode, Boolean pIsLeafNode)
        {
            TLzCustomExpression lcexpr = (TLzCustomExpression)pnode;
            if (lcexpr.AsText.Replace(" ", "").CompareTo(searchText.Replace(" ", "")) == 0)
            {
                lcexpr.AsText = " "; // one space
                return false; // stop searching
            }
            else
            {
                return true;
            }
        }

        public void add(conditionType t, string exprStr)
        {
            if (!findCondition(exprStr)){
                condition.AsText = condition.AsText+ " "+t.ToString()+" "+exprStr;
            }
        }

        bool findCondition(string exprStr)
        {
            isFound = false;
            searchText = exprStr;
            this.condition.InOrderTraverse(searchVisitor);
            return isFound;
        }

        string searchText, replaceText;
        bool isFound = false;

        public  Boolean searchVisitor(TLz_Node pnode, Boolean pIsLeafNode)
        {
            TLzCustomExpression lcexpr = (TLzCustomExpression)pnode;
            if (lcexpr.AsText.Replace(" ","").CompareTo(searchText.Replace(" ","")) == 0)
            {
                isFound = true;
                return false; // stop searching
            }
            else
            {
                return true;
            }
        }


        public void modify1()
        {
            this.condition.PreOrderTraverse(customizedModifyVisitor1);
            //pExpr.InOrderTraverse(treenodevisitor);
            //pExpr.PostOrderTraverse(treenodevisitor);

        }

        public Boolean customizedModifyVisitor1(TLz_Node pnode, Boolean pIsLeafNode)
        {
            TLzCustomExpression lcexpr;
            lcexpr = (TLzCustomExpression)pnode;
            String left, right;

            switch (lcexpr.oper)
            {
                case (TLzOpType.Expr_In):
                    {
                        left = lcexpr.lexpr.AsText + ".in";
                        right = lcexpr.rexpr.AsText.Substring(1, lcexpr.rexpr.AsText.Length - 2).Replace("'", "\"");
                        lcexpr.AsText = left + "[" + right + "]";
                        break;
                    }
                case (TLzOpType.Expr_AND):
                    {
                        right = lcexpr.rexpr.AsText;
                        //tblCustomerType.ID = tblPoints.BentekCustomerTypeID AND tblPipelines.ID = vwFlowDataBest.PipelineID
                        if (right.CompareTo("tblPipelines.ID = vwFlowDataBest.PipelineID") == 0)
                        {
                            lcexpr.AsText = lcexpr.lexpr.AsText;
                        }
                        break;
                    }
                default:
                    break;
            }
            return true;
        }

    }


}
